from Selenium2Library.keywords.keywordgroup import KeywordGroup
from Selenium2Library.locators import ElementFinder

import jaydebeapi
import jpype

class _DBKeywords(KeywordGroup):
    def __init__(self):
        """Does nothing. init only"""
        print '_DBKeywords.__init__'  
        
    def start_JVM(self):    
        jar = r'./SCBLibrary/db2jcc.jar'
        args='-Djava.class.path=%s' % jar
        
        jvm_path = jpype.getDefaultJVMPath()
        jpype.startJVM(jvm_path, args)        

    def connect_to_db2(self, url, user, pwd):
        return jaydebeapi.connect('com.ibm.db2.jcc.DB2Driver',
                                  'jdbc:db2://'+url, user, pwd)
        
    def close_db2_connection(self, conn):
        conn.close()
                
    def clear_transaction_DB2(self):
        ''' Used in make-transfer.tsv to clear "Today's" transaction history.
        
        So we decide to use clear DB method to restore the env for auto test.
        
        Delete user A's transaction of B account on 2011-10-01
        '''
        return self._run('clear_transaction_DB2')

    def _clear_transaction_DB2_pk(self):
        ''' For PK
        '''        
        #waiting for Thomas' SQLs to delete the transaction in DB2
        #sql = "select * from scbtxn.FT_TXN fetch first 10 rows only"
        sql_del_transfer = "delete from scbtxn.FT_TXN f WHERE f.EBID='PKTEST0000001'and f.dt_transfer like '2011-10-01%'"
        sql_del_bill = "delete from scbtxn.BILL_PMT_TXN b WHERE b.EBID='PKTEST0000001'and b.DT_PMT like '2011-10-01%'"
        sql_del_voucher = "delete from scbtxn.VOUCHER_TXN v WHERE v.EBID='PKTEST0000001'and v.DT_PMT like '2011-10-01%'"
        print sql_del_transfer
        print sql_del_bill
        print sql_del_voucher
        
        self.start_JVM()
              
#        conn = jaydebeapi.connect('com.ibm.db2.jcc.DB2Driver',
#                                  'jdbc:db2://10.112.179.227:50000/AEIBNKIT', 'ebkadmae', 'pass123')
        conn = self.connect_to_db2('10.112.179.227:50000/AEIBNKIT', 'ebkadmae', 'pass123')
        curs = conn.cursor()
        curs.execute(sql_del_transfer)
        curs.execute(sql_del_bill)
        curs.execute(sql_del_voucher)
        
        #print curs.fetchone()
        #for row in curs.fetchall():
        #    print row[0], '|', row[1], '|', row[3]
            
        curs.close()
        self.close_db2_connection(conn)   
        
